package com.matt.repository.impl;

import com.alibaba.fastjson.JSON;
import com.dotflat.fin.domain.FinPlanOrder;
import com.dotflat.fin.domain.PayRecord;
import com.dotflat.fin.dto.AppPayResult;
import com.matt.domain.Clientele;

import com.matt.domain.Contract;
import com.matt.foundation.constant.ClienteleConstant;
import com.matt.foundation.query.ClienteleQuery;
import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.utils.QueryUtil;
import com.matt.foundation.utils.UUidUtils;
import com.matt.foundation.vo.ClienteleListVO;
import com.matt.foundation.vo.ClienteleVo;
import com.matt.repository.base.ClienteleNativeRepository;
import com.matt.repository.base.BaseNativeSqlRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

import static com.matt.foundation.constant.LeadsHistoryConstant.LEADS_HISTORY_CLIENTELE;


/**
 * Created by Ron
 * from com.matt
 * on 2018/6/19 0019.
 */
@Service
@Transactional
public class ClienteleNativeRepositoryImpl extends BaseNativeSqlRepository implements ClienteleNativeRepository {

    private static Set<String> validKeys = null;

    {
        /**
         * 查询key:name(客户姓名) 、sysChildName(孩子系统名)、 mobile(客户电话)
         * englishName(英文名) 、nickName(昵称)、 childName(真实姓名)、 targetGarden(幼儿园)
         */
        String[] keys = {
                "name",
                "sysChildName",
                "mobile",
                "englishName",
                "nickName",
                "childName",
                "staffName"
        };
        validKeys = new HashSet<String>(Arrays.asList(keys));
    }

    private String conditionSqlByQuery(ClienteleQuery clienteleQuery) {

        String querySql = "";
        //我的客户
        if (StringUtils.isNotBlank(clienteleQuery.getStaffId()))
            querySql += String.format(" and cl.staffId = '%s'", clienteleQuery.getStaffId());

        //查询条件  过滤月龄
        String qkey = clienteleQuery.getQueryKey();
        String qval = clienteleQuery.getQueryVal();
        if (StringUtils.isNotBlank(qkey) && StringUtils.isNotBlank(qval)) {
            if ("monthAge".equals(qkey)) {
                querySql += String.format(" and ((YEAR(NOW()) - YEAR(cl.birthday)) * 12 + (MONTH(NOW()) - MONTH(cl.birthday)))=%s", qval);
            } else if (validKeys.contains(qkey)) {
                querySql += String.format(" and cl." + clienteleQuery.getQueryKey() + " like concat('%s','%s','%s') ", "%", QueryUtil.replaceAllSql(qval), "%");
            }
        }

        //状态查询
        if (StringUtils.isNotBlank(clienteleQuery.getStatus()))
            querySql += String.format(" and cl.status IN(%s)", clienteleQuery.getStatus());
        //客户成交的状态
        if (clienteleQuery.getSignStatus() != null)
            querySql += String.format(" and cl.signStatus='%s'", clienteleQuery.getSignStatus());

        //待补款客户
        if (StringUtils.isNotBlank(clienteleQuery.getContinueFillMoneyKey()) && "cfm".equals(clienteleQuery.getContinueFillMoneyKey()))
            querySql += String.format(" and cl.id IN(select DISTINCT(clienteleId) from contract where payStatus=0 )");

        //创建时间开始
        if (clienteleQuery.getBeginDate() != null) {
            querySql += String.format(" and cl.created >= '%s'", clienteleQuery.getBeginDate());
        }

        //创建时间结束
        if (clienteleQuery.getEndDate() != null) {
            querySql += String.format(" and cl.created < '%s'", clienteleQuery.getEndDate());
        }

        querySql += String.format(" and cl.deleted='0' and cl.areaId='%s'", clienteleQuery.getAreaId());

        return querySql;
    }

    @Override
    public List<Clientele> findByQuery(ClienteleQuery clienteleQuery) {
        String querySql = String.format("SELECT cl.id,cl.channel,cl.channelId as channelId,cl.fromUser,cl.category,cl.status,cl.sysChildName,cl.childName,cl.birthday," +
                "cl.nickName,cl.englishName,cl.gender,cl.bloodType,cl.nationality,cl.targetGarden,cl.registerResidence,cl.registerType,cl.signStatus as signStatus," +
                "cl.languageEnvironment,cl.name,cl.clienteleType,cl.relationship,cl.mobile,cl.homeAddress,cl.company,cl.duty,cl.degree," +
                "cl.staffId,cl.lastModified,cl.lastModifiedId,cl.areaId,cl.lastFollowTime,cl.lastFollowRecord,cl.adviceName,cl.staffName from clientele as cl where true");

        querySql += conditionSqlByQuery(clienteleQuery);

        //我的
        if (StringUtils.isNotBlank(clienteleQuery.getStaffId())) {
            querySql += String.format(" and cl.staffId='%s'", clienteleQuery.getStaffId());
        }

        querySql += String.format(" order by cl.created desc");
        querySql += String.format(" limit %s,%s ", clienteleQuery.getPageNum() * clienteleQuery.getPageSize(), clienteleQuery.getPageSize());
        List<Clientele> objectList = sqlObjectList(querySql, Clientele.class);
        return objectList;
    }

    @Override
    public Long count(ClienteleQuery clienteleQuery) {
        String querySql = String.format("SELECT count(cl.id) from clientele as cl where true");

        querySql += conditionSqlByQuery(clienteleQuery);

        //我的
        if (StringUtils.isNotBlank(clienteleQuery.getStaffId())) {
            querySql += String.format(" and cl.staffId='%s'", clienteleQuery.getStaffId());
        }

        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<Clientele> findChilds(String areaId, String childName) {
        String querySql = String.format("select cl.id,cl.channel,cl.channelId as channelId,cl.fromUser,cl.category,cl.status,cl.sysChildName,cl.childName,cl.birthday," +
                "cl.nickName,cl.englishName,cl.gender,cl.bloodType,cl.nationality,cl.targetGarden,cl.registerResidence,cl.registerType," +
                "cl.languageEnvironment,cl.name,cl.clienteleType,cl.relationship,cl.mobile,cl.homeAddress,cl.company,cl.duty,cl.degree," +
                "cl.staffId,cl.lastModified,cl.lastModifiedId,cl.areaId,cl.lastFollowTime,cl.lastFollowRecord,cl.adviceName,cl.signStatus as signStatus" +
                //"  from clientele as cl where cl.childName like concat('%s','%s') ",childName,"%");
                "  from clientele as cl where cl.childName='%s'", childName);
        querySql += String.format(" and cl.deleted='0' and cl.areaId='%s'", areaId);
        querySql += String.format(" order by cl.created desc");
        List<Clientele> objectList = sqlObjectList(querySql, Clientele.class);
        return objectList;
    }

    @Override
    public List<ClienteleVo> continueClienteles(ClienteleQuery clienteleQuery) {
        String querySql = String.format("SELECT cl.id,cl.channel,cl.channelId as channelId,cl.fromUser,cl.category,cl.status,cl.sysChildName,cl.childName,cl.birthday," +
                "cl.nickName,cl.englishName,cl.gender,cl.bloodType,cl.nationality,cl.targetGarden,cl.registerResidence,cl.registerType," +
                "cl.languageEnvironment,cl.name,cl.clienteleType,cl.relationship,cl.mobile,cl.homeAddress,cl.company,cl.duty,cl.degree,cl.signStatus as signStatus," +
                "cl.staffId,cl.lastModified,cl.lastModifiedId,cl.areaId,cl.lastFollowTime,cl.lastFollowRecord,cl.adviceName,cl.staffName from clientele as cl where true");

        //我的客户
        if (StringUtils.isNotBlank(clienteleQuery.getStaffId()))
            querySql += String.format(" and cl.staffId = '%s'", clienteleQuery.getStaffId());

        if (StringUtils.isNotBlank(clienteleQuery.getStatus()))
            querySql += String.format(" and cl.status IN(%s)", clienteleQuery.getStatus());

        if (StringUtils.isNotBlank(clienteleQuery.getClienteleType()))
            querySql += String.format(" and cl.clienteleType IN(%s)", clienteleQuery.getClienteleType());

        //待续约客户
        if (StringUtils.isNotBlank(clienteleQuery.getContinueKey()) && "ck".equals(clienteleQuery.getContinueKey())) {
            //托班时间小于30天
            querySql += String.format(" and cl.id IN(select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId " +
                    " inner join contract as con on con.id=cp.contractId where pr.type=1 and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=1 and cli.areaId='%s'", clienteleQuery.getAreaId());
            querySql += String.format(" union");
            //早教小于8节
            querySql += String.format(" select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId " +
                    " INNER JOIN contract as con ON cp.contractId=con.id where pr.type=2 and con.status=1 and cp.remainCount<8 and cp.remainCount>0 and cli.areaId='%s'", clienteleQuery.getAreaId());
            querySql += String.format(")");
        }

        querySql += String.format(" and cl.deleted='0' and cl.areaId='%s'", clienteleQuery.getAreaId());
        querySql += String.format(" order by cl.created desc");
        querySql += String.format(" limit %s,%s ", clienteleQuery.getPageNum() * clienteleQuery.getPageSize(), clienteleQuery.getPageSize());
        List<ClienteleVo> objectList = sqlObjectList(querySql, ClienteleVo.class);
        return objectList;

    }

    @Override
    public Long countContinueClienteles(ClienteleQuery clienteleQuery) {
        String querySql = String.format("SELECT count(cl.id) from clientele as cl where true");

        //我的客户
        if (StringUtils.isNotBlank(clienteleQuery.getStaffId()))
            querySql += String.format(" and cl.staffId = '%s'", clienteleQuery.getStaffId());

        //待续约客户
        if (StringUtils.isNotBlank(clienteleQuery.getContinueKey()) && "ck".equals(clienteleQuery.getContinueKey())) {
            //托班时间小于30天
            querySql += String.format(" and cl.id IN(select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId " +
                    " inner join contract as con on con.id=cp.contractId where pr.type=1 and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=1 and cli.areaId='%s'", clienteleQuery.getAreaId());
            querySql += String.format(" union");
            //早教小于8节
            querySql += String.format(" select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId " +
                    " INNER JOIN contract as con ON cp.contractId=con.id where pr.type=2 and con.status=1 and cp.remainCount<8 and cp.remainCount>0 and cli.areaId='%s'", clienteleQuery.getAreaId());
            querySql += String.format(")");
        }
        querySql += String.format("  and cl.deleted='0'");
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<Object[]> findClienteleProducts(String clienteleId) {
        /********  1. 详情版  *******************/
       /* //早教
       String querySql=String.format("SELECT group_concat(productName SEPARATOR '|') AS productName,group_concat(remainCount SEPARATOR '|') AS remainCount  FROM (select group_concat(pr.name separator '|') as productName,group_concat(cp.remainCount separator '|') as remainCount from contract_product as cp inner join product as pr on cp.productId=pr.id" +
               " inner join contract as con on con.id=cp.contractId where  cp.clienteleId='%s' ",clienteleId);
       querySql +=String.format(" and cp.remainCount < 8  AND TO_DAYS(NOW()) >= TO_DAYS(con.beginDate) and cp.type=2 and pr.type=2");
       //托班
        querySql +=String.format(" UNION ");
        querySql +=String.format(" select group_concat(pr.name separator '|') as productName,group_concat(TO_DAYS(con.endDate)-TO_DAYS(now()) separator '|') as remainCount from contract_product as cp inner join product as pr on cp.productId=pr.id inner join contract as con on cp.contractId = con.id " +
                " where  cp.clienteleId='%s' ",clienteleId);
        querySql +=String.format(" and TO_DAYS(con.endDate) - TO_DAYS(NOW()) > 0 AND TO_DAYS(con.endDate) - TO_DAYS(NOW()) < 30 AND TO_DAYS(NOW()) >= TO_DAYS(con.beginDate) AND con. STATUS = 0 and cp.type=2 and pr.type=1) as productInfo");*/

        /********  2. 汇总版  *******************/
        //早教
        String querySql = String.format("SELECT group_concat(productName SEPARATOR '|') AS productName,group_concat(remainCount SEPARATOR '|') AS remainCount  FROM (select DISTINCT(pr.name) as productName,SUM(cp.remainCount) as remainCount from contract_product as cp inner join product as pr on cp.productId=pr.id" +
                " inner join contract as con on con.id=cp.contractId where  cp.clienteleId='%s' ", clienteleId);
        querySql += String.format(" and cp.remainCount < 8  AND con.STATUS=1 and cp.type=2 and pr.type=2  GROUP BY cp.productId");
        //托班
        querySql += String.format(" UNION ");
        querySql += String.format(" select DISTINCT(pr.name) as productName,SUM(TO_DAYS(con.endDate) - TO_DAYS(now())) as remainCount from contract_product as cp inner join product as pr on cp.productId=pr.id inner join contract as con on cp.contractId = con.id " +
                " where  cp.clienteleId='%s' ", clienteleId);
        querySql += String.format(" and TO_DAYS(con.endDate) - TO_DAYS(NOW()) > 0 AND TO_DAYS(con.endDate) - TO_DAYS(NOW()) < 30 AND con.STATUS = 1 and cp.type=2 and pr.type=1  GROUP BY cp.productId) as productInfo");

        List<Object[]> objects = sqlArrayList(querySql);
        return objects;
    }

    @Override
    public List<Object[]> findLastPurchaseInfo(String clienteleId) {
        String querySql = String.format("select group_concat(cp.created separator '|') as created ,group_concat(pr.name separator '|') as name,group_concat(cp.count separator '|') as count,group_concat(pr.unit separator '|') as unit  from contract_product as cp inner join product as pr on cp.productId=pr.id where cp.type=2 and cp.clienteleId='%s'", clienteleId);
        querySql += String.format(" order by cp.created desc limit 1");
        List<Object[]> objects = sqlArrayList(querySql);
        return objects;
    }

    @Override
    public Page<ClienteleListVO> findByName(String name, String areaId, List<String> clientIds, Pageable pageable) {
        String querySql = String.format("select id,name,mobile,childName,birthday,relationship," +
                " TIMESTAMPDIFF(MONTH, birthday, now())  as month" +
                " from clientele" +
                " where areaId='%s'" +
                " and deleted='0' ", areaId);
        if (StringUtils.isNotBlank(name)) {
            //name, mobile,childName,company,homeAddress
            querySql += String.format(" and (name like concat('%s','%s','%s') ", "%", name, "%");
            querySql += String.format(" or mobile like concat('%s','%s','%s') ", "%", name, "%");
            querySql += String.format(" or childName like concat('%s','%s','%s') ", "%", name, "%");
            querySql += String.format(" or company like concat('%s','%s','%s') ", "%", name, "%");
            querySql += String.format(" or homeAddress like concat('%s','%s','%s') )", "%", name, "%");
        }

        if (clientIds != null && clientIds.size() > 0) {
            String param = JSON.toJSONString(clientIds);
            param = param.substring(1, param.length() - 1);

            querySql += String.format(" and id in (%s)", param);
        }

        String countSql = querySql.substring(querySql.indexOf(" from "));
        countSql = "select count(id) " + countSql;

        //分页
        querySql += String.format(" limit %s,%s ", pageable.getPageNumber() * pageable.getPageSize(), pageable.getPageSize());

        Long count = sqlLongResult(countSql);

        List<ClienteleListVO> list = sqlObjectList(querySql, ClienteleListVO.class);

        return new PageImpl<>(list, pageable, count);
    }

    @Override
    public Clientele findBySystemChildNameAndId(String id, String sysChildName, String areaId) {
        String querySql = String.format("select cl.id,cl.channel,cl.channelId as channelId,cl.fromUser,cl.category,cl.status,cl.sysChildName,cl.childName,cl.birthday," +
                "cl.nickName,cl.englishName,cl.gender,cl.bloodType,cl.nationality,cl.targetGarden,cl.registerResidence,cl.registerType," +
                "cl.languageEnvironment,cl.name,cl.clienteleType,cl.relationship,cl.mobile,cl.homeAddress,cl.company,cl.duty,cl.degree," +
                "cl.staffId,cl.lastModified,cl.lastModifiedId,cl.areaId,cl.lastFollowTime,cl.lastFollowRecord,cl.adviceName,cl.signStatus as signStatus" +
                "  from clientele as cl where true and cl.sysChildName='%s'", sysChildName);
        if (StringUtils.isNotBlank(id))
            querySql += String.format(" and cl.id !='%s'", id);
        querySql += String.format(" and cl.areaId='%s'", areaId);
        List<Clientele> objectList = sqlObjectList(querySql, Clientele.class);

        return objectList.size() > 0 ? objectList.get(0) : null;
    }

    @Override
    public List<Clientele> findByChildNameAndAreaId(String childName, String areaId) {
        String querySql = String.format("select cl.id,cl.channel,cl.channelId as channelId,cl.fromUser,cl.category,cl.status,cl.sysChildName,cl.childName,cl.birthday," +
                "cl.nickName,cl.englishName,cl.gender,cl.bloodType,cl.nationality,cl.targetGarden,cl.registerResidence,cl.registerType," +
                "cl.languageEnvironment,cl.name,cl.clienteleType,cl.relationship,cl.mobile,cl.homeAddress,cl.company,cl.duty,cl.degree," +
                "cl.staffId,cl.lastModified,cl.lastModifiedId,cl.areaId,cl.lastFollowTime,cl.lastFollowRecord,cl.adviceName,cl.signStatus as signStatus" +
                "  from clientele as cl where cl.childName like concat('%s','%s','%s')", "%", childName, "%");
        querySql += String.format(" and cl.areaId='%s'", areaId);
        List<Clientele> objectList = sqlObjectList(querySql, Clientele.class);
        return objectList;
    }

    @Override
    public Long followClienteleNum(String staffId, Date begin, Date end) {
        String querySql = String.format("SELECT count(DISTINCT(ls.leadsId)) from leads_history as ls WHERE ls.operationType='follow' and date(ls.created) >= date('%s') and date(ls.created) <= date('%s') and ls.staffId='%s'", DateUtil.dateToymd(begin), DateUtil.dateToymd(end), staffId);
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long viewClienteleNum(String staffId, Date begin, Date end) {
        String querySql = String.format("select count(DISTINCT(clientelesId)) from leads_history where staffId='%s' and date(created) >= date('%s') and date(created) <= date('%s') and clienteleStatus='%s' and type='%s'", staffId, DateUtil.dateToymd(begin), DateUtil.dateToymd(end), ClienteleConstant.Status.ARRIVED.codeOf(), LEADS_HISTORY_CLIENTELE);
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long increaseClienteleNum(String staffId, Date begin, Date end) {
        String querySql = String.format("select count(DISTINCT(id)) from clientele where staffId='%s' and date(created) >= date('%s') and date(created) <= date('%s')", staffId, DateUtil.dateToymd(begin), DateUtil.dateToymd(end));
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<Object[]> findChannelCount(String areaId, String date) {
        String querySql = "SELECT channel,COUNT(id) as channelCount,areaId  FROM `clientele` where areaId = '" + areaId + "' and DATE_FORMAT(created,'%Y-%m') = DATE_FORMAT('" + date + "','%Y-%m') and channel != '' GROUP BY channel ";
        List<Object[]> objects = sqlArrayList(querySql);
        return objects;
    }

    @Override
    public List<Contract> findByClienteleIdIn(String clienteleIds) {
        String querySql = String.format("select `id`, `created`, `deleted`, `areaId`, `beginDate`, " +
                " `clienteleId`, `code`, `endDate`, `fromChannel`, `isRenewal`, `lastModified`, `lastModifiedId`, `name`, " +
                "`paidDate`, `payChannel`, `payChannelVal`, `payStatus`,  `payType`, `payTypeVal`, `practicalAmount`,  " +
                "`remark`, `signDate`, `staffId`, `staffName`, `status`, `statusVal`, `totalAmount`, `type`, " +
                "  `surplusLeaveDay`  from contract  where  ");
        querySql += String.format("  clienteleId in (%s)", clienteleIds);
        List<Contract> objectList = sqlObjectList(querySql, Contract.class);
        return objectList;
    }

    @Override
    public Page<PayRecord> childrenIdByPayRecord(List<String> ids, Pageable pageable) {
        String ids_2 = UUidUtils.getString(ids);
        String querySql = "SELECT  pr.created,pr.id,pr.createdBy,pr.payOrderId,pr.payType,pr.payStatus,pr.payId,pr.payTime as attach " +
                "  FROM `fin_pay_record` pr  LEFT JOIN fin_pay_order po on pr.payOrderId = po.id WHERE po.clienteleId in (" + ids_2 + ")";

        String countSql = "SELECT COUNT(pr.id) FROM `fin_pay_record` pr  LEFT JOIN fin_pay_order po on pr.payOrderId = po.id WHERE po.clienteleId in (" + ids_2 + ")";
        Long count = sqlLongResult(countSql);
        //分页
        querySql += String.format(" limit %s,%s ", pageable.getPageNumber() * pageable.getPageSize(), pageable.getPageSize());

        List<PayRecord> list = sqlObjectList(querySql, PayRecord.class);

        String payOrderSql = "SELECT  po.id,po.planName,po.title,po.payType,po.childName,po.orderSn,po.totalAmount,po.createdByName,po.clienteleId,po.modifyTime as paiedTime,po.modifyTime   " +
                "  FROM `fin_pay_record` pr  LEFT JOIN fin_pay_order po on pr.payOrderId = po.id WHERE po.clienteleId in (" + ids_2 + ")";
        List<FinPlanOrder> finPlanOrderList = sqlObjectList(payOrderSql, FinPlanOrder.class);

        list.stream().forEach(o -> {
            finPlanOrderList.stream().forEach(q -> {
                if (StringUtils.equals(o.getPayOrderId(), q.getId())) {
                    o.setFinPlanOrder(q);
                    o.setPayTime(q.getPaiedTime());
                    AppPayResult appPayResult = new AppPayResult();
                    appPayResult.setTimeEnd(o.getAttach());
                    appPayResult.setTransactionId(o.getPayId());
                    o.setPayResult(appPayResult);
                }
            });
        });
        return new PageImpl<>(list, pageable, count);
    }
}
